﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Xml.Linq;

namespace Dating
{
    class Dating
    {
        static void Main(string[] args)
        {
            //GettingDataFromXML();
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; Database=DatingDatabase; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand command = new SqlCommand(
                    "SELECT p.Name, l.Name as MatchName " +
                    "FROM Persons p "+
                    "JOIN Preferences m "+
                    "ON p.PersonID=m.PersonID "+
                    "JOIN Persons l "+
                    "ON l.Height>=m.MinHeight AND l.Height<=m.MaxHeight AND l.HairColor=m.HairColor AND "+
                    "l.EyesColor = m.EyesColor AND l.Age>=m.MinAge AND l.Age<=m.MaxAge and l.Gender=m.Gender", dbCon);
                var reader = command.ExecuteReader();
                List<XElement> matches = new List<XElement>();
                while(reader.Read())
                {
                    XElement element = new XElement("date",
                        new XElement("person", reader["Name"].ToString()),
                        new XElement("person", reader["MatchName"].ToString())
                        );
                    matches.Add(element);
                }
                XElement matchesXml = new XElement("dates", matches);

                System.Console.WriteLine(matchesXml);

                //booksXml.Save("../../dates.xml");

                //TRY USING XMLWRITER FOR THIS> HELPS ADDING ATTRIBUTES
            }

        }

        private static void GettingDataFromXML()
        {
            DatingDatabaseEntities1 dbEntity = new DatingDatabaseEntities1();
            using (XmlReader reader = XmlReader.Create("../../people.xml"))
            {
                while (reader.Read())
                {
                    if ((reader.NodeType == XmlNodeType.Element) &&
                        (reader.Name == "guys"))
                    {
                        string gender = "guy";
                        AddPerson(dbEntity, reader, gender);
                    }

                    if ((reader.NodeType == XmlNodeType.Element) &&
                        (reader.Name == "girls"))
                    {
                        string gender = "girl";
                        AddPerson(dbEntity, reader, gender);
                    }
                }

                dbEntity.SaveChanges();
            }
        }

        private static void AddPerson(DatingDatabaseEntities1 dbEntity, XmlReader reader, string gender)
        {
            while (reader.Read())
            {
                if ((reader.NodeType == XmlNodeType.Element) &&
                    (reader.Name == "person"))
                {
                    Person newPerson = new Person();
                    newPerson.Name = reader.GetAttribute(0);
                    newPerson.HairColor = reader.GetAttribute(1);
                    newPerson.EyesColor = reader.GetAttribute(2);
                    newPerson.Height = int.Parse(reader.GetAttribute(3));
                    newPerson.Age = int.Parse(reader.GetAttribute(4));
                    newPerson.Gender = gender;
                    dbEntity.Persons.AddObject(newPerson);

                    AddPreferences(dbEntity, reader, newPerson);
                }
                if ((reader.NodeType == XmlNodeType.EndElement) && (reader.Name == "guys"))
                {
                    break;
                }
            }
        }

        private static void AddPreferences(DatingDatabaseEntities1 dbEntity, XmlReader reader, Person newPerson)
        {
            Preference personPreference = new Preference();            

            while (reader.Read())
            {
                if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "girl"))
                {
                    personPreference.Gender = "girl";
                }
                if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "guy"))
                {
                    personPreference.Gender = "guy";
                }
                if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "height"))
                {
                    string text = reader.ReadString();
                    personPreference.MaxHeight = GetMaxNumber(text);
                    personPreference.MinHeight = GetMinNumber(text);
                }
                if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "haircolor"))
                {
                    personPreference.HairColor = reader.ReadString();
                }
                if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "eyecolor"))
                {
                    personPreference.EyesColor = reader.ReadString();
                }
                if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "age"))
                {
                    string text = reader.ReadString();
                    personPreference.MaxAge = GetMaxNumber(text);
                    personPreference.MinAge = GetMinNumber(text);
                }
                if ((reader.NodeType == XmlNodeType.EndElement) && ((reader.Name == "girl") || (reader.Name == "guy")))
                {
                    newPerson.Preferences.Add(personPreference);

                    personPreference = new Preference();            
                }
                if ((reader.NodeType == XmlNodeType.EndElement) &&
                    (reader.Name == "preferences"))
                {
                    break;
                }
            }            
        }

        private static int GetMinNumber(string numbers)
        {
            string pattern = @"\A[\d]+";
            string text = Regex.Match(numbers, pattern).ToString();
            int result = int.Parse(text);
            return result;
        }

        private static int GetMaxNumber(string numbers)
        {
            string pattern = @"[0-9]+\Z";
            string text = Regex.Match(numbers, pattern).ToString();
            int result = int.Parse(text);
            return result;
        }
    }
}
